<%@ page import="com.sun.xml.internal.ws.policy.spi.AbstractQNameValidator" %>
<%@ page import="jxl.JXLException" %>
<%@ page import="jxl.format.*" %>
<%@ page import="jxl.write.*" %>
<%@ page import="jxl.format.Border" %>
<%@ page import="jxl.format.Colour" %>
<%@ page import="jxl.format.Alignment" %>
<%@ page import="jxl.format.BorderLineStyle" %>
<%@ page import="jxl.format.VerticalAlignment" %>
<%@ page pageEncoding="UTF-8" %>
<%!
    /**
     * 每个小模块下的都有此文件，用来封装后台方法，这样方法之间调用会比较好，但是耦合性太强，
     * 测试文件单独放一个文件夹
     */

    //补全身份证号
    public String fixPersonIDCode(String personIDCode) {
        if (personIDCode == null || personIDCode.trim().length() != 15) {
            return personIDCode;
        }
        String id17 = personIDCode.substring(0, 6) + "19"
                + personIDCode.substring(6, 15); // 15为身份证补\'19\'
        char[] code = {'1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'}; // 11个
        int[] factor = {0, 2, 4, 8, 5, 10, 9, 7, 3, 6, 1, 2, 4, 8, 5, 10, 9, 7}; // 18个;
        int[] idcd = new int[18];
        int i;
        int j;
        int sum;
        int remainder;
        for (i = 1; i < 18; i++) {
            j = 17 - i;
            idcd[i] = Integer.parseInt(id17.substring(j, j + 1));
        }
        sum = 0;
        for (i = 1; i < 18; i++) {
            sum = sum + idcd[i] * factor[i];
        }
        remainder = sum % 11;
        String lastCheckBit = String.valueOf(code[remainder]);
        return id17 + lastCheckBit;
    }

    /**
     * 判断是否存在该医院通讯录数据
     * @param input
     * @return
     */
    public Message addressCheck(Input input) {
        Integer hospital = input.getInt("hospital", -999);
        Message msg = new Message();
        List<Long> dataList = QBuilder.db("basedb").queryHql("select count(*) from hospital_user where hospital=" + hospital + " and status=" + STATUS_ENABLE);
        msg.put("data", dataList.get(0));//数目为0表示没有存储过数据，不为0则表示存储过数据，需要提示是否确认覆盖
        return msg;
    }

    /**
     * 导入通讯录的excel数据，批量存储
     * @param input
     * @return
     * @throws Exception
     */
    public Message addressExcel(Input input) throws Exception {
        String urlFileName = input.getDesc("urlFileName", "");
        Integer hospital = input.getInt("hospital", -999);
        Message msg = new Message();//用于存储结果数据
        String fileName = urlFileName.substring(urlFileName.lastIndexOf("/") + 1);
        urlFileName = urlFileName.substring(0, urlFileName.lastIndexOf("/")) + "/" + java.net.URLEncoder.encode(fileName, "utf-8");
        jxl.Workbook workbook;
        InputStream is = null;
        Integer errNum = 0;
        try {
            URL url = new URL(urlFileName);//获取excel地址对象
            URLConnection urlConnection = url.openConnection();
            urlConnection.connect();
            HttpURLConnection httpURLConnection = (HttpURLConnection) urlConnection;
            if (httpURLConnection.getResponseCode() != HttpURLConnection.HTTP_OK) {
                msg.setErrcodeAndErrmsg(23333, "URL地址解析异常");
                return msg;
            } else {
                //解析正确，首先逻辑删除该医院所有的通讯录
                QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_DISABLE + " where hospital=" + hospital);
                //逻辑删除科室关联表
                QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_DISABLE + " where hospital=" + hospital);
                //逻辑删除标签关联表,type=1 表示人员关联
                QBuilder.db("basedb").execHQL("update hospital_label_member  set status=" + STATUS_DISABLE + " where hospital=" + hospital + " and type=1");
                //获取医院的基础标签id
                List<Map> labelList = QBuilder.db("basedb").queryHql("select new map(id as label_id,name as label_name) from hospital_label_category where hospital=0 and status=" + STATUS_ENABLE);
                String titleCategoryId = "";
                String dutyCategoryId = "";
                for (Map labelMap : labelList) {//遍历
                    if ((labelMap.get("label_name") + "").equals("职称")) {
                        titleCategoryId = labelMap.get("label_id") + "";
                    } else if ((labelMap.get("label_name") + "").equals("职位")) {
                        dutyCategoryId = labelMap.get("label_id") + "";
                    }
                }
                //读取文件流
                is = httpURLConnection.getInputStream();
                workbook = jxl.Workbook.getWorkbook(is);
                jxl.Sheet sheet = workbook.getSheet(0);//获取第一张sheet表
                int rows = sheet.getRows();//获取行数
                Map addUser;//用于存放单条存储记录
                for (int r = 1; r < rows; r++) {//循环获取数据记录
                    errNum = r;
                    String employeeId = sheet.getCell(0, r).getContents();//职工编号
                    String name = sheet.getCell(1, r).getContents();//职工姓名
                    String cardNumber = sheet.getCell(2, r).getContents();//身份证
                    String phone = sheet.getCell(3, r).getContents();//手机号
                    String wechat = sheet.getCell(4, r).getContents();//微信
                    String qq = sheet.getCell(5, r).getContents();//QQ
                    String email = sheet.getCell(6, r).getContents();//邮箱
                    String deptName = sheet.getCell(7, r).getContents();//科室名称
                    String title = sheet.getCell(8, r).getContents();//职称
                    String dutyName = sheet.getCell(9, r).getContents();//职位
                    String password = "e10adc3949ba59abbe56e057f20f883e";//默认密码
                    Integer gender = 1;
                    String avatar = DEFAULT_AVATAR;//默认头像
          /*
           * 数据的验证活动，暂时省略
           */
                    if (name.equals("") && phone.equals("")) {//验证数据是否为空
                        msg.setErrcodeAndErrmsg(23333, "第" + (r + 1) + "行数据异常，空数据");
                        //物理删除人员新增数据
                        QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                        //物理删除科室关联表新增数据
                        QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                        //物理删除标签关联表新增数据,type=1 表示人员关联
                        QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
                        //逻辑恢复人员表
                        QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                        //逻辑恢复科室关联表
                        QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                        //逻辑恢复标签关联表,type=1 表示人员关联
                        QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
                        return msg;
                    }
                    if (!phone.equals("")) {//手机号是否为空
                        //不为空则手机号数据判重
                        List<String> checkPhoneList = QBuilder.db("basedb").queryHql("select id from hospital_user where phone='" + phone + "' and status=" + STATUS_ENABLE);
                        if (checkPhoneList.size() > 0) {//存在该手机号，忽略该条记录
                            msg.setErrcodeAndErrmsg(23333, "第" + (r + 1) + "行数据异常，手机号已被占用");
                            //物理删除人员新增数据
                            QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                            //物理删除科室关联表新增数据
                            QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                            //物理删除标签关联表新增数据,type=1 表示人员关联
                            QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
                            //逻辑恢复人员表
                            QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                            //逻辑恢复科室关联表
                            QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                            //逻辑恢复标签关联表,type=1 表示人员关联
                            QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
                            return msg;
                        }
                    }
                    //不存在该手机号，继续录入
                    if (!cardNumber.equals("")) {//身份证号获取性别，默认性别为男 1男0女
                        //补全身份证号码
                        cardNumber = fixPersonIDCode(cardNumber);
                        // if(IdCardUtil.validateIdCard18(cardNumber)){//校验身份证号码合法性
                        //   //合法，判断性别、默认头像
                        //   gender = IdCardUtil.getGender(cardNumber)%2;
                        //   avatar = gender == 0 ? USER_AVATAR_FM : USER_AVATAR_M;
                        // }//不合法也可以存储进数据库，后期管理员可以修改
                    }
                    //存储进数据库
                    addUser = new HashMap();
                    addUser.put("name", name);
                    addUser.put("employee_id", employeeId);
                    addUser.put("card_number", cardNumber);
                    addUser.put("phone", phone);
                    addUser.put("wechat", wechat);
                    addUser.put("qq", qq);
                    addUser.put("email", email);
                    addUser.put("password", password);
                    addUser.put("gender", gender);
                    addUser.put("avatar", avatar);
                    addUser.put("hospital", hospital);
                    addUser.put("status", STATUS_ENABLE);
                    addUser.put("create_time", System.currentTimeMillis());
                    addUser.put("json", "{}");
                    addUser.put("hidden", 0);
                    Serializable userKey = DBTools.db("basedb").addItem("hospital_user", addUser);//获取到id
                    if (userKey == null) {//存储失败
                        msg.setErrcodeAndErrmsg(23333, "第" + (r + 1) + "行数据异常，用户数据存储失败");
                        //物理删除人员新增数据
                        QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                        //物理删除科室关联表新增数据
                        QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                        //物理删除标签关联表新增数据,type=1 表示人员关联
                        QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
                        //逻辑恢复人员表
                        QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                        //逻辑恢复科室关联表
                        QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                        //逻辑恢复标签关联表,type=1 表示人员关联
                        QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
                        return msg;
                    }
                    //存储成功
                    if (!deptName.equals("")) {//判断科室名是否为空
                        //判断是否已存过该科室
                        List<Integer> checkDeptList = QBuilder.db("basedb").queryHql("select id from Dept where hospital=" + hospital + " and type=20 and status=" + STATUS_ENABLE + " and deptName='" + deptName + "'");
                        Integer deptId = 0;
                        if (checkDeptList.size() == 0) {//未存储过，进行存储
                            msg.setErrcodeAndErrmsg(23333, "第" + (r + 1) + "行数据异常，未查询到该科室记录");
                            //物理删除人员新增数据
                            QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                            //物理删除科室关联表新增数据
                            QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                            //物理删除标签关联表新增数据,type=1 表示人员关联
                            QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
                            //逻辑恢复人员表
                            QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                            //逻辑恢复科室关联表
                            QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                            //逻辑恢复标签关联表,type=1 表示人员关联
                            QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
                            return msg;
                        } else {//存储过,获取id
                            deptId = checkDeptList.get(0);
                        }
                        if (deptId != 0) {//存入中间表
                            Map deptUserRelationMap = new HashMap();
                            deptUserRelationMap.put("dept_id", deptId);
                            deptUserRelationMap.put("user_id", (Integer) userKey);
                            deptUserRelationMap.put("hospital", hospital);
                            deptUserRelationMap.put("status", STATUS_ENABLE);
                            deptUserRelationMap.put("create_time", System.currentTimeMillis());
                            deptUserRelationMap.put("json", "{}");
                            Serializable deptRelationKey = DBTools.db("basedb").addItem("hospital_dept_user", deptUserRelationMap);
                            if (deptRelationKey == null) {//存儲失敗
                                msg.setErrcodeAndErrmsg(23333, "第" + (r + 1) + "行数据异常，科室关系数据存储失败");
                                //物理删除人员新增数据
                                QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                                //物理删除科室关联表新增数据
                                QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                                //物理删除标签关联表新增数据,type=1 表示人员关联
                                QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
                                //逻辑恢复人员表
                                QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                                //逻辑恢复科室关联表
                                QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                                //逻辑恢复标签关联表,type=1 表示人员关联
                                QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
                                return msg;
                            }//存儲成功
                        }
                    } else {
                        msg.setErrcodeAndErrmsg(23333, "第" + (r + 1) + "行数据异常，科室名称为空");
                        //物理删除人员新增数据
                        QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                        //物理删除科室关联表新增数据
                        QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                        //物理删除标签关联表新增数据,type=1 表示人员关联
                        QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
                        //逻辑恢复人员表
                        QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                        //逻辑恢复科室关联表
                        QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                        //逻辑恢复标签关联表,type=1 表示人员关联
                        QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
                        return msg;
                    }
                    //根據职称职位获取标签id
                    if (!title.equals("")) {//职称判空，不为空，则查询
                        List<Map> titleList = QBuilder.db("basedb").queryHql("select new map(id as label_id,name as label_name) from hospital_label where hospital=" + hospital + " and status=" + STATUS_ENABLE + " and category=" + titleCategoryId);
                        boolean titleFlag = false;
                        String titleId = "";
                        for (Map titleMap : titleList) {//遍历list
                            if ((titleMap.get("label_name") + "").equals(title)) {//判断该职称是否存在，存在获取titleId
                                titleId = titleMap.get("label_id") + "";
                                titleFlag = true;
                                break;
                            }
                        }
                        if (titleFlag == false) {//未存在，则存储
                            Map titleMap = new HashMap();
                            titleMap.put("name", title);
                            titleMap.put("category", Integer.valueOf(titleCategoryId));
                            titleMap.put("hosptial", 999);
                            titleMap.put("status", STATUS_ENABLE);
                            titleMap.put("create_time", System.currentTimeMillis());
                            titleMap.put("json", "{}");
                            Serializable titleKey = DBTools.db("basedb").addItem("hospital_label", titleMap);
                            if (titleKey == null) {//存储失败
                                msg.setErrcodeAndErrmsg(23333, "第" + (r + 1) + "行数据异常，职称数据存储失败");
                                //物理删除人员新增数据
                                QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                                //物理删除科室关联表新增数据
                                QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                                //物理删除标签关联表新增数据,type=1 表示人员关联
                                QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
                                //逻辑恢复人员表
                                QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                                //逻辑恢复科室关联表
                                QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                                //逻辑恢复标签关联表,type=1 表示人员关联
                                QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
                                return msg;
                            } else {//存储成功，拿到titleId
                                titleId = titleKey + "";
                            }
                        }
                        //存储职称关系表
                        Map titleRelationMap = new HashMap();
                        titleRelationMap.put("label_id", Integer.valueOf(titleId));
                        titleRelationMap.put("member_id", (Integer) userKey);
                        titleRelationMap.put("type", 1);
                        titleRelationMap.put("hospital", hospital);
                        titleRelationMap.put("status", STATUS_ENABLE);
                        titleRelationMap.put("create_time", System.currentTimeMillis());
                        titleRelationMap.put("json", "{}");
                        Serializable titleRelationKey = DBTools.db("basedb").addItem("hospital_label_member", titleRelationMap);
                        if (titleRelationKey == null) {//存储失败
                            msg.setErrcodeAndErrmsg(23333, "第" + (r + 1) + "行数据异常，职称关联数据存储失败");
                            //物理删除人员新增数据
                            QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                            //物理删除科室关联表新增数据
                            QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                            //物理删除标签关联表新增数据,type=1 表示人员关联
                            QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
                            //逻辑恢复人员表
                            QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                            //逻辑恢复科室关联表
                            QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                            //逻辑恢复标签关联表,type=1 表示人员关联
                            QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
                            return msg;
                        }//存储成功
                    }
                    if (!dutyName.equals("")) {//职位判空，不为空，则查询
                        List<Map> dutyList = QBuilder.db("basedb").queryHql("select new map(id as label_id,name as label_name) from hospital_label where hospital=" + hospital + " and status=" + STATUS_ENABLE + " and category=" + dutyCategoryId);
                        boolean dutyFlag = false;
                        String dutyId = "";
                        for (Map dutyMap : dutyList) {//遍历list
                            if ((dutyMap.get("label_name") + "").equals(dutyName)) {//判断该职位是否存在，存在获取dutyId
                                dutyId = dutyMap.get("label_id") + "";
                                dutyFlag = true;
                                break;
                            }
                        }
                        if (dutyFlag == false) {//未存在，则存储
                            Map dutyMap = new HashMap();
                            dutyMap.put("name", dutyName);
                            dutyMap.put("category", Integer.valueOf(dutyCategoryId));
                            dutyMap.put("hosptial", 999);
                            dutyMap.put("status", STATUS_ENABLE);
                            dutyMap.put("create_time", System.currentTimeMillis());
                            dutyMap.put("json", "{}");
                            Serializable dutyKey = DBTools.db("basedb").addItem("hospital_label", dutyMap);
                            if (dutyKey == null) {//存储失败
                                msg.setErrcodeAndErrmsg(23333, "第" + (r + 1) + "行数据异常，职位数据存储失败");
                                //物理删除人员新增数据
                                QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                                //物理删除科室关联表新增数据
                                QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                                //物理删除标签关联表新增数据,type=1 表示人员关联
                                QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
                                //逻辑恢复人员表
                                QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                                //逻辑恢复科室关联表
                                QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                                //逻辑恢复标签关联表,type=1 表示人员关联
                                QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
                                return msg;
                            } else {//存储成功，拿到dutyId
                                dutyId = dutyKey + "";
                            }
                        }
                        //存储职位关系表
                        Map dutyRelationMap = new HashMap();
                        dutyRelationMap.put("label_id", Integer.valueOf(dutyId));
                        dutyRelationMap.put("member_id", (Integer) userKey);
                        dutyRelationMap.put("type", 1);
                        dutyRelationMap.put("hospital", hospital);
                        dutyRelationMap.put("status", STATUS_ENABLE);
                        dutyRelationMap.put("create_time", System.currentTimeMillis());
                        dutyRelationMap.put("json", "{}");
                        Serializable dutyRelationKey = DBTools.db("basedb").addItem("hospital_label_member", dutyRelationMap);
                        if (dutyRelationKey == null) {//存储失败
                            msg.setErrcodeAndErrmsg(23333, "第" + (r + 1) + "行数据异常，职位关联数据存储失败");
                            //物理删除人员新增数据
                            QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                            //物理删除科室关联表新增数据
                            QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
                            //物理删除标签关联表新增数据,type=1 表示人员关联
                            QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
                            //逻辑恢复人员表
                            QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                            //逻辑恢复科室关联表
                            QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
                            //逻辑恢复标签关联表,type=1 表示人员关联
                            QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
                            return msg;
                        }//存储成功
                    }//为空，略过
                }
                //物理删除人员原数据
                QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_DISABLE + " and hospital=" + hospital);
                //物理删除科室关联表原数据
                QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_DISABLE + " and hospital=" + hospital);
                //物理删除标签关联表原数据,type=1 表示人员关联
                QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_DISABLE + " and hospital=" + hospital + " and type=1");
            }
        } catch (Exception e) {
            msg.setErrcodeAndErrmsg(23333, "第" + (errNum + 1) + "行数据异常");
            //物理删除人员新增数据
            QBuilder.db("basedb").execHQL("delete from hospital_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
            //物理删除科室关联表新增数据
            QBuilder.db("basedb").execHQL("delete from hospital_dept_user where status=" + STATUS_ENABLE + " and hospital=" + hospital);
            //物理删除标签关联表新增数据,type=1 表示人员关联
            QBuilder.db("basedb").execHQL("delete from hospital_label_member where status=" + STATUS_ENABLE + " and hospital=" + hospital + " and type=1");
            //逻辑恢复人员表
            QBuilder.db("basedb").execHQL("update hospital_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
            //逻辑恢复科室关联表
            QBuilder.db("basedb").execHQL("update hospital_dept_user set status=" + STATUS_ENABLE + " where hospital=" + hospital);
            //逻辑恢复标签关联表,type=1 表示人员关联
            QBuilder.db("basedb").execHQL("update hospital_label_member set status=" + STATUS_ENABLE + " where hospital=" + hospital + " and type=1");
            e.printStackTrace();
        } finally {
            if (is != null) {//关闭文件流
                is.close();
            }
            return msg;//返回结果
        }
    }

    /**
     * 递归查询子集科室列表
     * @param deptList
     * @param deptId
     * @return
     */
    public List checkId(List<Map> deptList, String deptId) {
        List<String> idList = new ArrayList<>();
        idList.add(deptId);//本身的id
        for (Map deptMap : deptList) {
            String parent = deptMap.get("parent") + "";
            String id = deptMap.get("id") + "";
            if (parent.equals(deptId + "")) {//科室的上级科室等于传进来id
                idList.add(id);
                idList.addAll(checkId(deptList, id));
            }
        }
        return idList;
    }

    /**
     * 根据部门编号读取通讯录人员（分页）
     * @param input
     * @return
     */
    public Message addressQueryByPage(Input input) {
        Integer hospital = input.getInt("hospital", -999);
        String deptId = input.getDesc("deptId", "-1");
        Integer pageNum = input.getInt("page_num", 1);//当前页，从1开始
        Integer itemCount = 10;//记录条数
        Message msg = new Message();
        Map dataMap = new HashMap();
        //获取记录
        List<Map> allList = QBuilder.db("basedb").queryHql("select new map(id as id,deptName as deptName,parent as parent) from Dept where hospital=" + hospital + " and type=20 and status=" + STATUS_ENABLE);
        List<String> idList = checkId(allList, deptId);
        //将list转为字符串
        StringBuffer sb = new StringBuffer(idList.get(0));
        for (int i = 1; i < idList.size(); i++) {
            sb.append("," + idList.get(i));
        }
        String idStr = sb.toString();
        //查询人员
        List<Map> userList = QBuilder.db("basedb").select("new map(u.id as id,u.name as name,u.employee_id as employee_id,u.card_number as card_number,u.phone as phone,u.wechat as wechat,u.qq as qq,u.email as email,u.hidden as hidden,u.gender as gender,u.avatar as avatar,u.last_ip as last_ip,u.last_login as last_login,d.id as dept_id,d.deptName as deptName)").from("hospital_user u,hospital_dept_user du,Dept d").where(new Where("u.hospital=", hospital).and("u.status=", STATUS_ENABLE).and("du.dept_id in (" + idStr + ")").and("du.user_id=u.id").and("du.hospital=", hospital).and("du.status=", STATUS_ENABLE).and("du.dept_id=d.id").and("d.status=", STATUS_ENABLE).and("d.hospital=", hospital)).page(itemCount, pageNum).query().getResultObj();
        String singleDeptIdStr = "-1";
        List<String> mergeList = new ArrayList<>();
        List<String> singleDeptIdList = new ArrayList<>();
        for (int i = 0; i < userList.size(); i++) {
            Map userMap = userList.get(i);
            Integer listNum = mergeList.indexOf(userMap.get("id") + "");
            if (listNum > -1) {//userMap已经出现过了，合并，并且不再请求重复数据
                Map firstMap = userList.get(listNum);
                firstMap.put("deptName", firstMap.get("deptName") + "," + userMap.get("deptName"));
                singleDeptIdList.addAll(getParentDeptList(allList,userMap.get("dept_id")+""));
                userList.remove(i);
                continue;
            } else {//存到mergeList中
                mergeList.add(userMap.get("id") + "");
                singleDeptIdList = getParentDeptList(allList,userMap.get("dept_id") + "");
            }
        }
        HashSet hashSet = new HashSet(singleDeptIdList);
        singleDeptIdList.clear();
        singleDeptIdList.addAll(hashSet);
        if(singleDeptIdList.size()>0){//有数据
            for(String singleId:singleDeptIdList){
                singleDeptIdStr += ","+singleId;
            }
            singleDeptIdStr = singleDeptIdStr.substring(1);
        }
        //获取人员的连带数据
        for (int i = 0; i < userList.size(); i++) {
            Map userMap = userList.get(i);
            //获取基础标签
            List<Map> basicList = QBuilder.db("basedb").select("new map(l.id as label_id,l.name as label_name,lc.name as category_name)").from("hospital_label_category lc,hospital_label_member lm,hospital_label l").where(new Where("l.category=lc.id").and("lm.member_id=", userMap.get("id") + "").and("lm.label_id=l.id").and("lm.type=1").and("lm.status=", STATUS_ENABLE).and("l.hospital=", hospital).and("l.status=", STATUS_ENABLE)).query().getResultObj();
            userMap.put("basic", basicList);
            //获取扩展标签
            List<Map> extendList = QBuilder.db("basedb").select("distinct new map(l.id as label_id,l.name as label_name)").from("hospital_label_member lm,hospital_label l").where(new Where("l.category=0").and("(lm.member_id=" + userMap.get("id") + " and lm.type=1) or (lm.type=2 and lm.member_id in (" + singleDeptIdStr + "))").and("lm.label_id=l.id").and("lm.status=", STATUS_ENABLE).and("l.hospital=", hospital).and("l.status=", STATUS_ENABLE)).query().getResultObj();
            userMap.put("extend", extendList);
        }
        dataMap.put("user", userList);
        //获取总页数
        List<Long> totalNumList = QBuilder.db("basedb").select("count(distinct u.id)").from("hospital_user u,hospital_dept_user du,Dept d").where(new Where("u.hospital=", hospital).and("u.status=", STATUS_ENABLE).and("du.dept_id in (" + idStr + ")").and("du.user_id=u.id").and("du.hospital=", hospital).and("du.status=", STATUS_ENABLE).and("du.dept_id=d.id").and("d.status=", STATUS_ENABLE).and("d.hospital=", hospital)).query().getResultObj();
        Long totalNum = totalNumList.get(0);
        dataMap.put("total_page", ((totalNum - 1) / itemCount) + 1);
        msg.put("data", dataMap);
        return msg;
    }

    /**
     * 根据部门编号读取通讯录人员
     * @param input
     * @return
     */
    public Message addressQuery(Input input) {
        Integer hospital = input.getInt("hospital", -999);
        String deptId = input.getDesc("deptId", "-1");
        Message msg = new Message();
        //获取记录
        List<Map> allList = QBuilder.db("basedb").queryHql("select new map(id as id,deptName as deptName,parent as parent) from Dept where hospital=" + hospital + " and type=20 and status=" + STATUS_ENABLE);
        List<String> idList = checkId(allList, deptId);
        //将list转为字符串
        StringBuffer sb = new StringBuffer(idList.get(0));
        for (int i = 1; i < idList.size(); i++) {
            sb.append("," + idList.get(i));
        }
        String idStr = sb.toString();
        //查询人员
        List<Map> userList = QBuilder.db("basedb").select("new map(u.id as id,u.name as name,u.employee_id as employee_id,u.card_number as card_number,u.phone as phone,u.wechat as wechat,u.qq as qq,u.email as email,u.hidden as hidden,u.gender as gender,u.avatar as avatar,u.last_ip as last_ip,u.last_login as last_login,d.deptName as deptName)").from("hospital_user u,hospital_dept_user du,Dept d").where(new Where("u.hospital=", hospital).and("u.status=", STATUS_ENABLE).and("du.dept_id in (" + idStr + ")").and("du.user_id=u.id").and("du.hospital=", hospital).and("du.status=", STATUS_ENABLE).and("du.dept_id=d.id").and("d.status=", STATUS_ENABLE).and("d.hospital=", hospital)).query().getResultObj();
        List<String> mergeList = new ArrayList<>();
        //获取人员的连带数据
        for (int i = 0; i < userList.size(); i++) {
            Map userMap = userList.get(i);
            Integer listNum = mergeList.indexOf(userMap.get("id") + "");
            if (listNum > -1) {//userMap已经出现过了，合并，并且不再请求重复数据
                Map firstMap = userList.get(listNum);
                firstMap.put("deptName", firstMap.get("deptName") + "," + userMap.get("deptName"));
                userList.remove(i);
                continue;
            } else {//存到mergeList中
                mergeList.add(userMap.get("id") + "");
            }
            //获取基础标签
            List<Map> basicList = QBuilder.db("basedb").select("new map(l.id as label_id,l.name as label_name,lc.name as category_name)").from("hospital_label_category lc,hospital_label_member lm,hospital_label l").where(new Where("l.category=lc.id").and("lm.member_id=", userMap.get("id") + "").and("lm.label_id=l.id").and("lm.type=1").and("lm.status=", STATUS_ENABLE).and("l.hospital=", hospital).and("l.status=", STATUS_ENABLE)).query().getResultObj();
            userMap.put("basic", basicList);
            //获取扩展标签
            List<Map> extendList = QBuilder.db("basedb").select("new map(l.id as label_id,l.name as label_name)").from("hospital_label_member lm,hospital_label l").where(new Where("l.category=0").and("lm.member_id=", userMap.get("id") + "").and("lm.label_id=l.id").and("lm.type=1").and("lm.status=", STATUS_ENABLE).and("l.hospital=", hospital).and("l.status=", STATUS_ENABLE)).query().getResultObj();
            userMap.put("extend", extendList);
        }
        msg.put("data", userList);
        return msg;
    }

    /**
     * 新增成员
     */
    public Message addressAdd(Input input) {
        Message msg = new Message();
        Integer hospital = input.getInt("hospital", -999);
        String employeeId = input.getDesc("employee_id", "");//工号
        String name = input.getDesc("name", "");//姓名
        String phone = input.getDesc("phone", "");//手机
        String depts = input.getDesc("depts", "");//科室列表
        String titleId = input.getDesc("title_id", "");//职称标号
        String dutyId = input.getDesc("duty_id", "");//职位标号
        //默认字段
        String password = "e10adc3949ba59abbe56e057f20f883e";//默认密码
        Integer gender = 1;
        String avatar = DEFAULT_AVATAR;//默认头像
        //字段判断

        //存入通讯录
        Map userMap = new HashMap();
        userMap.put("employee_id", employeeId);
        userMap.put("name", name);
        userMap.put("phone", phone);
        userMap.put("password", password);
        userMap.put("gender", gender);
        userMap.put("avatar", avatar);
        userMap.put("hospital", hospital);
        userMap.put("status", STATUS_ENABLE);
        userMap.put("create_time", System.currentTimeMillis());
        userMap.put("json", "{}");
        userMap.put("hidden", 0);
        Serializable userKey = DBTools.db("basedb").addItem("hospital_user", userMap);//获取到id
        if (userKey == null) {//存储失败
            msg.setErrcodeAndErrmsg(23333, "用户数据存储失败");
        } else {//存储成功
            String[] deptArray = depts.split(",");
            if (deptArray.length > 0) {//存在科室
                List<Map> deptUserRelationList = new ArrayList<>();
                //存储科室，循环
                for (String deptId : deptArray) {
                    //存入关系表
                    Map deptUserRelationMap = new HashMap();
                    deptUserRelationMap.put("dept_id", Integer.valueOf(deptId));
                    deptUserRelationMap.put("user_id", (Integer) userKey);
                    deptUserRelationMap.put("hospital", hospital);
                    deptUserRelationMap.put("status", STATUS_ENABLE);
                    deptUserRelationMap.put("create_time", System.currentTimeMillis());
                    deptUserRelationMap.put("json", "{}");
                    deptUserRelationList.add(deptUserRelationMap);
                }
                List<Integer> deptRelationKeyList = DBTools.db("basedb").addItem("hospital_dept_user", deptUserRelationList);
                if (deptRelationKeyList.isEmpty()) {//存儲失敗
                    msg.setErrcodeAndErrmsg(23333, "科室关系数据存储失败");
                } else {//存儲成功
                    //存储职称关系表
                    Map titleRelationMap = new HashMap();
                    titleRelationMap.put("label_id", Integer.valueOf(titleId));
                    titleRelationMap.put("member_id", (Integer) userKey);
                    titleRelationMap.put("type", 1);
                    titleRelationMap.put("hospital", hospital);
                    titleRelationMap.put("status", STATUS_ENABLE);
                    titleRelationMap.put("create_time", System.currentTimeMillis());
                    titleRelationMap.put("json", "{}");
                    Serializable titleRelationKey = DBTools.db("basedb").addItem("hospital_label_member", titleRelationMap);
                    if (titleRelationKey == null) {//存储失败
                        msg.setErrcodeAndErrmsg(23333, "职称关联数据存储失败");
                    }//存储成功
                    //存储职位关系表
                    Map dutyRelationMap = new HashMap();
                    dutyRelationMap.put("label_id", Integer.valueOf(dutyId));
                    dutyRelationMap.put("member_id", (Integer) userKey);
                    dutyRelationMap.put("type", 1);
                    dutyRelationMap.put("hospital", hospital);
                    dutyRelationMap.put("status", STATUS_ENABLE);
                    dutyRelationMap.put("create_time", System.currentTimeMillis());
                    dutyRelationMap.put("json", "{}");
                    Serializable dutyRelationKey = DBTools.db("basedb").addItem("hospital_label_member", dutyRelationMap);
                    if (dutyRelationKey == null) {//存储失败
                        msg.setErrcodeAndErrmsg(23333, "职位关联数据存储失败");
                    }//存储成功
                }
            } else {//不存在科室，报错
                msg.setErrcodeAndErrmsg(23333, "请选择相关科室信息");
            }
        }
        return msg;
    }

    /**
     * 根据部门编号获取上级路径
     * @param deptList
     * @param deptId
     * @return
     */
    public String getDeptPath(List<Map> deptList, String deptId) {
        String result = "";
        for (Map deptMap : deptList) {
            String id = deptMap.get("id") + "";
            String parent = deptMap.get("parent") + "";
            if (id.equals(deptId)) {//找到该条记录，根据父节点查询
                result = deptMap.get("deptName") + "";
                if (!parent.equals("-1")) {//存在父节点
                    result = getDeptPath(deptList, parent) + "-" + result;
                }
                break;
            }
        }
        return result;
    }

    /**
     * 根据部门编号获取上级科室list
     * @param deptList
     * @param deptId
     * @return
     */
    public List<String> getParentDeptList(List<Map> deptList, String deptId) {
        List<String> result = new ArrayList<>();
        for (Map deptMap : deptList) {
            String id = deptMap.get("id") + "";
            String parent = deptMap.get("parent") + "";
            if (id.equals(deptId)) {//找到该条记录，根据父节点查询
                result.add(id + "");
                if (!parent.equals("-1")) {//存在父节点
                    result.addAll(getParentDeptList(deptList, parent));
                }
                break;
            }
        }
        return result;
    }

    /**
     * 模糊查询方法
     * @param input
     * @return
     */
    public Message fuzzySearch(Input input) {
        Message msg = new Message();
        String key = input.getDesc("key", "").replace("/", "//").replace("\\", "/\\\\").replace("_", "/_").replace("%", "/%");
        Integer hospital = input.getInt("hospital", -999);
        Map resultMap = new HashMap();
        String str = "employee_id like '%" + key + "%' escape '/' or name like '%" + key + "%' escape '/'";
        if (Tools.isNumeric(key)) {//是数字包含手机号
            str += " or phone like '%" + key + "%'";
        }
        //查询通讯录(编号、名称、手机号)、科室、标签
        List<Map> addressList = QBuilder.db("basedb").select().from("hospital_user").where(new Where("hospital=", hospital).and("status=", STATUS_ENABLE).and(str)).query().getResultObj();
        //获取记录
        List<Map> allList = QBuilder.db("basedb").queryHql("select new map(id as id,deptName as deptName,parent as parent) from Dept where hospital=" + hospital + " and type=20 and status=" + STATUS_ENABLE);
        //获取人员的连带数据
        for (Map userMap : addressList) {
            //获取科室信息
            List<Map> deptList = QBuilder.db("basedb").select("new map(dept_id as dept_id)").from("hospital_dept_user").where(new Where("hospital=", hospital).and("status=", STATUS_ENABLE).and("user_id=", userMap.get("id") + "")).query().getResultObj();
            String singleDeptIdStr = "-1";
            List<String> singleDeptIdList = new ArrayList<>();
            for (Map deptMap : deptList) {
                String deptPath = getDeptPath(allList, deptMap.get("dept_id") + "");//获取路径
                deptMap.put("deptPath", deptPath);
                singleDeptIdList.addAll(getParentDeptList(allList,deptMap.get("dept_id")+""));
            }
            HashSet hashSet = new HashSet(singleDeptIdList);
            singleDeptIdList.clear();
            singleDeptIdList.addAll(hashSet);
            if(singleDeptIdList.size()>0){//有数据
                for(String singleId:singleDeptIdList){
                    singleDeptIdStr += ","+singleId;
                }
                singleDeptIdStr = singleDeptIdStr.substring(1);
            }
            userMap.put("dept", deptList);
            //获取基础标签
            List<Map> basicList = QBuilder.db("basedb").select("new map(l.id as label_id,l.name as label_name,lc.name as category_name)").from("hospital_label_category lc,hospital_label_member lm,hospital_label l").where(new Where("l.category=lc.id").and("lm.member_id=", userMap.get("id") + "").and("lm.label_id=l.id").and("lm.type=1").and("lm.status=", STATUS_ENABLE).and("l.hospital=", hospital).and("l.status=", STATUS_ENABLE)).query().getResultObj();
            userMap.put("basic", basicList);
            //获取扩展标签
            List<Map> extendList = QBuilder.db("basedb").select("distinct new map(l.id as label_id,l.name as label_name)").from("hospital_label_member lm,hospital_label l").where(new Where("l.category=0").and("(lm.member_id=" + userMap.get("id") + " and lm.type=1) or (lm.type=2 and lm.member_id in (" + singleDeptIdStr + "))").and("lm.label_id=l.id").and("lm.status=", STATUS_ENABLE).and("l.hospital=", hospital).and("l.status=", STATUS_ENABLE)).query().getResultObj();
            userMap.put("extend", extendList);
        }
        resultMap.put("user", addressList);
        List<Map> deptList = QBuilder.db("basedb").select("new map(id as id,deptName as deptName,parent as parent)").from("Dept").where(new Where("hospital=", hospital).and("status=", STATUS_ENABLE).and("type=20").and("deptName like '%" + key + "%' escape '/'")).query().getResultObj();
        for (Map deptMap : deptList) {
            String deptPath = getDeptPath(allList, deptMap.get("id") + "");
            deptMap.put("deptPath", deptPath);
            List<String> idList = checkId(allList, deptMap.get("id") + "");
            deptMap.put("deptNum", idList.size());//获取科室数目，最少是1，所以肯定可查询
            String userIdStr = idList.get(0);
            for (int i = 0; i < idList.size(); i++) {
                userIdStr += "," + idList.get(i);
            }
            List<Long> userNumList = QBuilder.db("basedb").select("count(distinct user_id)").from("hospital_dept_user").where(new Where("hospital=", hospital).and("status=", STATUS_ENABLE).and("dept_id in (" + userIdStr + ")")).query().getResultObj();
            deptMap.put("userNum", userNumList.get(0));
        }
        resultMap.put("dept", deptList);
        List<Map> labelList = QBuilder.db("basedb").select().from("hospital_label").where(new Where("hospital=", hospital).and("status=", STATUS_ENABLE).and("name like '%" + key + "%' escape '/' ")).query().getResultObj();
        for (Map labelMap : labelList) {//循环
            List<Map> numMap = QBuilder.db("basedb").select("new map(count(member_id) as num,type as type)").from("hospital_label_member").where(new Where("hospital=", hospital).and("status=", STATUS_ENABLE).and("label_id=", labelMap.get("id") + "")).group("type").query().getResultObj();
            labelMap.put("num", numMap);
        }
        resultMap.put("label", labelList);
        msg.put("data", resultMap);
        return msg;
    }

    /**
     * 获取职称和职位列表
     * @param input
     * @return
     */
    public Message titleAndDutyList(Input input) {
        Message msg = new Message();
        Integer hospital = input.getInt("hospital", -999);
        //根据医院编号获取职称和职位
        List<Map> dataList = QBuilder.db("basedb").select("new map(id as id,name as name)").from("hospital_label_category").where(new Where("name in ('职称','职位')").and("status=", STATUS_ENABLE)).query().getResultObj();
        if (dataList.size() > 0) {
            Map resultMap = new HashMap();
            for (Map dataMap : dataList) {
                List<Map> subList = QBuilder.db("basedb").select("new map(id as id,name as name,alias as alias)").from("hospital_label").where(new Where("hospital=", hospital).and("status=", STATUS_ENABLE).and("category=", dataMap.get("id") + "")).query().getResultObj();
                resultMap.put(dataMap.get("name") + "", subList);
            }
            msg.put("data", resultMap);
        } else {
            msg.setErrcodeAndErrmsg(23333, "未查询到相关职称职位信息");
        }
        return msg;
    }

    /**
     * 根据编号和类别获取详细信息
     * @param input
     * @return
     */
    public Message addressDetail(Input input) {
        Message msg = new Message();
        Integer hospital = input.getInt("hospital", -999);
        String addressType = input.getDesc("address_type", "");
        String addressId = input.getDesc("address_id", "");
        String tableName = addressType.equals("user") ? "hospital_user" : (addressType.equals("dept") ? "dept" : "hospital_label");
        List<Map> detailList = QBuilder.db("basedb").select().from(tableName).where(new Where("hospital=", hospital).and("status=", STATUS_ENABLE).and("id=", addressId)).query().getResultObj();
        if (detailList.size() > 0) {//拿到数据
            msg.put("data", detailList.get(0));
        } else {
            msg.setErrcodeAndErrmsg(23333, "未查询到相关信息");
        }
        return msg;
    }

    /**
     * 获取所有的通讯录数据，并导出
     * @param input
     * @return
     */
    public Message addressOutput(Input input) {
        Message msg = new Message();
        Integer hospital = input.getInt("hospital", -999);
        HttpServletRequest request = input.getRequest();
        String filePath = request.getSession().getServletContext().getRealPath("/") + "excels/";//获取下载路径
        //获取所有通讯录数据
        List<Map> allList = QBuilder.db("basedb").select("new map(id as id,employee_id as employee_id,name as name,card_number as card_number,phone as phone,wechat as wechat,qq as qq,email as email)").from("hospital_user").where(new Where("hospital=", hospital).and("status=", STATUS_ENABLE)).order("employee_id", "asc").query().getResultObj();
        for (Map allMap : allList) {//循环遍历
            //获取科室
            List<String> deptList = QBuilder.db("basedb").select("d.deptName").from("Dept d,hospital_dept_user du").where(new Where("du.hospital=", hospital).and("du.status=", STATUS_ENABLE).and("du.dept_id=d.id").and("d.hospital=", hospital).and("d.status=", STATUS_ENABLE).and("du.user_id=", allMap.get("id") + "")).query().getResultObj();
            String deptStr = "";
            if (deptList.size() > 0) {
                deptStr = deptList.get(0);
                for (int i = 1; i < deptList.size(); i++) {
                    deptStr += "," + deptList.get(i);
                }
            }
            allMap.put("dept", deptStr);
            //获取职称/职位
            List<Map> basicList = QBuilder.db("basedb").select("new map(l.id as label_id,l.name as label_name,lc.name as category_name)").from("hospital_label_category lc,hospital_label_member lm,hospital_label l").where(new Where("l.category=lc.id").and("lm.member_id=", allMap.get("id") + "").and("lm.label_id=l.id").and("lm.type=1").and("lm.status=", STATUS_ENABLE).and("l.hospital=", hospital).and("l.status=", STATUS_ENABLE)).query().getResultObj();
            String titleStr = "";
            String dutyStr = "";
            for (Map basicMap : basicList) {//循环
                if ((basicMap.get("category_name") + "").equals("职称")) {
                    titleStr += "," + basicMap.get("label_name");
                } else if ((basicMap.get("category_name") + "").equals("职位")) {
                    dutyStr += "," + basicMap.get("label_name");
                }
            }
            allMap.put("title", titleStr.length() > 0 ? titleStr.substring(1, titleStr.length()) : titleStr);
            allMap.put("duty", dutyStr.length() > 0 ? dutyStr.substring(1, dutyStr.length()) : dutyStr);
        }
        if (allList.size() > 0) {//有数据
            String title = "通讯录" + DateUtil.formatDateByFormat(new Date(), "yyyyMMddHHmmss") + ".xls";
            filePath += title;
            boolean flag = createTable(allList, filePath);
            if (flag) {
                String url = "http://" + request.getHeader("host") + "/excels/" + title;
                msg.put("data", url);
            } else {
                msg.setErrcodeAndErrmsg(23333, "导出失败");
            }
        } else {
            msg.setErrcodeAndErrmsg(23333, "未获取到相关数据");
        }
        return msg;
    }

    /**
     * 导出excel方法
     * @param dataList
     * @param filePath
     * @return
     */
    public boolean createTable(List<Map> dataList, String filePath) {
        boolean flag = true;
        jxl.write.WritableWorkbook book;
        try {
            String directoryPath = filePath.substring(0, filePath.lastIndexOf("/") + 1);
            File directory = new File(directoryPath);
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.MONTH, -1);
            final String existName = "通讯录" + DateUtil.formatDateByFormat(cal.getTime(), "yyyyMM");
            File[] files = directory.listFiles(new FilenameFilter() {
                @Override
                public boolean accept(File dir, String name) {
                    return name.contains(existName) && name.endsWith(".xls");
                }
            });
            for (File existFile : files) {//删除上月文件
                existFile.delete();
            }
            //创建文件对象
            File file = new File(filePath);
            //根据路径生成excel文件
            book = jxl.Workbook.createWorkbook(file);
            //创建一个sheet名为报销合计的表
            jxl.write.WritableSheet sheet = book.createSheet("通讯录", 0);
            int columns = 10;
            for (int i = 0; i < columns; i++) {
                sheet.setColumnView(i, 20);
            }
            sheet.setColumnView(2, 25);//身份证宽度
            jxl.write.Label tempLabel;
            //表头输出
            tempLabel = new jxl.write.Label(0, 0, "职工编号", getHeaderCellStyle());
            sheet.addCell(tempLabel);
            tempLabel = new jxl.write.Label(1, 0, "职工姓名", getHeaderCellStyle());
            sheet.addCell(tempLabel);
            tempLabel = new jxl.write.Label(2, 0, "身份证号", getHeaderCellStyle());
            sheet.addCell(tempLabel);
            tempLabel = new jxl.write.Label(3, 0, "手机号", getHeaderCellStyle());
            sheet.addCell(tempLabel);
            tempLabel = new jxl.write.Label(4, 0, "微信号", getHeaderCellStyle());
            sheet.addCell(tempLabel);
            tempLabel = new jxl.write.Label(5, 0, "QQ", getHeaderCellStyle());
            sheet.addCell(tempLabel);
            tempLabel = new jxl.write.Label(6, 0, "邮箱", getHeaderCellStyle());
            sheet.addCell(tempLabel);
            tempLabel = new jxl.write.Label(7, 0, "科室", getHeaderCellStyle());
            sheet.addCell(tempLabel);
            tempLabel = new jxl.write.Label(8, 0, "职称", getHeaderCellStyle());
            sheet.addCell(tempLabel);
            tempLabel = new jxl.write.Label(9, 0, "职位", getHeaderCellStyle());
            sheet.addCell(tempLabel);
            //body循环输出
            for (int i = 1; i <= dataList.size(); i++) {
                Map dataMap = dataList.get(i - 1);
                tempLabel = new jxl.write.Label(0, i, dataMap.get("employee_id") + "", getBodyCellStyle());
                sheet.addCell(tempLabel);
                tempLabel = new jxl.write.Label(1, i, dataMap.get("name") + "", getBodyCellStyle());
                sheet.addCell(tempLabel);
                tempLabel = new jxl.write.Label(2, i, dataMap.get("card_number") + "", getBodyCellStyle());
                sheet.addCell(tempLabel);
                tempLabel = new jxl.write.Label(3, i, dataMap.get("phone") + "", getBodyCellStyle());
                sheet.addCell(tempLabel);
                tempLabel = new jxl.write.Label(4, i, dataMap.get("wechat") + "", getBodyCellStyle());
                sheet.addCell(tempLabel);
                tempLabel = new jxl.write.Label(5, i, dataMap.get("qq") + "", getBodyCellStyle());
                sheet.addCell(tempLabel);
                tempLabel = new jxl.write.Label(6, i, dataMap.get("email") + "", getBodyCellStyle());
                sheet.addCell(tempLabel);
                tempLabel = new jxl.write.Label(7, i, dataMap.get("dept") + "", getBodyCellStyle());
                sheet.addCell(tempLabel);
                tempLabel = new jxl.write.Label(8, i, dataMap.get("title") + "", getBodyCellStyle());
                sheet.addCell(tempLabel);
                tempLabel = new jxl.write.Label(9, i, dataMap.get("duty") + "", getBodyCellStyle());
                sheet.addCell(tempLabel);
            }
            book.write();
            book.close();
        } catch (Exception e) {
            flag = false;
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 头部样式
     * @return
     */
    public WritableCellFormat getHeaderCellStyle() {
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 12,
                WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
        WritableCellFormat headerFormat = new WritableCellFormat(
                NumberFormats.TEXT);
        try {
            // 添加字体设置
            headerFormat.setFont(font);
            // 设置单元格背景色：表头为黄色
            headerFormat.setBackground(Colour.YELLOW);
            // 设置表头表格边框样式
            // 整个表格线为粗线、黑色
            headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN,
                    Colour.BLACK);
            // 表头内容水平居中显示
            headerFormat.setAlignment(Alignment.CENTRE);
        } catch (WriteException e) {
        }
        return headerFormat;
    }

    /**
     * body的样式
     * @return
     */
    public WritableCellFormat getBodyCellStyle() {
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 12,
                WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
        WritableCellFormat bodyFormat = new WritableCellFormat(font);
        try {
            // 设置单元格背景色：表体为白色
            bodyFormat.setBackground(Colour.WHITE);
            // 设置表头表格边框样式
            // 整个表格线为细线、黑色
            bodyFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            bodyFormat.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
            bodyFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//把垂直对齐方式指定为居中
        } catch (WriteException e) {
        }
        return bodyFormat;
    }
%>
